#!/usr/bin/env python3
from __future__ import print_function
import xlrd
from datetime import datetime, date
import chsex
import fill
import re
import sys
import os

if len(sys.argv) != 4:
    print('	{} [input.excel] [start-row-number] [total]'.format(sys.argv[0]))
    sys.exit(-1)

start = int(sys.argv[2]) - 1
total = int(sys.argv[3]) 
today = date.today().strftime('%Y-%m-%d') + '-apostl'
dstdir = os.path.join("out", today)
if not os.path.exists(dstdir):
    os.makedirs(dstdir)

rb = xlrd.open_workbook(sys.argv[1])
sheet = rb.sheet_by_index(0)
rows = sheet.nrows
cols =  sheet.ncols
#0 empty 1 string 2 number 3 date
#for x in range(start, rows):
for x in range(start, start + total):
    shit = False
    number  = -1
    dualtest = False
    test_date = ""
    test_date_month = 0
    test_date_day = 0
    test_date_year = 2020
    report_date  = ""
    report_date_month = 0
    report_date_day = 0
    report_date_year = 2020
    first_name, last_name = "", ""
    birth = ""
    gender = ""
    tel = ""
    email = ""
    licence  = ""
    address = ""
 
    city  = ""
    street = ""
    zipcode = ""
    state = ""

    for y in range(cols):
        cell = sheet.cell(x, y)
        if y == 0 and cell.ctype == 2:
            number = int(cell.value)
            print(number, end="|")
        if y == 0 and cell.ctype == 1:
            dualhit = cell.value
            if dualhit.lower().find("dual") >= 0:
                dualtest = True
            print(dualhit, end="|")
        if y == 1 and cell.ctype == 3:
            cell_tuple = xlrd.xldate_as_tuple(cell.value, datemode=0)
            test_date = (date(*cell_tuple[:3]).strftime('%m/%d/%Y'))
            test_date_month = "{:02d}".format(cell_tuple[1])
            test_date_day = "{:02d}".format(cell_tuple[2])
            test_date_year = cell_tuple[0]
            print(test_date, end="|")

        if y == 2 and cell.ctype == 3:
            cell_tuple = xlrd.xldate_as_tuple(cell.value, datemode=0)
            report_date = (date(*cell_tuple[:3]).strftime('%m/%d/%Y'))
            report_date_month = "{:02d}".format(cell_tuple[1])
            report_date_day = "{:02d}".format(cell_tuple[2])
            report_date_year = cell_tuple[0]
            print(report_date, end="|")

        if y == 4 and cell.ctype == 1:
            how = cell.value.count(' ')
            second_name = ""
            if how == 1:
                first_name, last_name = cell.value.split(' ')
            elif how == 2:
                first_name, second_name, last_name = cell.value.split(' ')
            first_name = first_name + " " + second_name
            first_name = first_name.strip()
            last_name = last_name.strip()
            print(first_name , last_name, end="|")

        if y == 5 and cell.ctype == 3:
            cell_tuple = xlrd.xldate_as_tuple(cell.value, datemode=0)
            birth = (date(*cell_tuple[:3]).strftime('%m/%d/%Y'))
            print(birth, end="|")
        if y == 6 and cell.ctype == 1:
            gender = cell.value
            gender = gender.lower()
            print(gender, end="|")
        if y == 7 and cell.ctype == 1:
            tel = cell.value
            print(tel, end="|")
        if y == 7 and cell.ctype == 2:
            tmp = cell.value
            tel = "{:.0f}".format(tmp) 
            print(tel, end="|")
        if y == 8 and cell.ctype == 1:
            email = cell.value
            print(email, end="|")
        if y == 9 and cell.ctype == 1:
            licence = cell.value
            print(licence, end="|")
        if y == 9 and cell.ctype == 2:
            licence = "{:.0f}".format(cell.value)
            print(licence, end="|")
        if y == 10 and cell.ctype ==1 :
            address = cell.value
            print(address )
    if number == -1:
        number =  x + 1
    tmp = re.search("[0-9]{5}$", address)
    if tmp:
        zipcode = tmp.group()
    else:
        shit = True
    tmp = re.findall("[A-Z]{2}",address)
    if tmp:
        state = tmp[-1]
    else:
        shit = True

    #tmp = re.search("([A-Z][a-z]+\s?)+,?\s?[A-Z]{2},?\s?\d{5}",address)
    tmp = re.search("(([A-Z][a-z]+\s?){1,2}),?\s?[A-Z]{2},?\s?\d{5}",address)
    if tmp:
       city = tmp.group(1)
    else:
        shit = True

    if len(city) > 0:
        where = address.find(city)
        street = address[:where]

    if len(street) < 10:
        where = address.find(state)
        street = address[:where]
        if len(street) < 10:
            street = address

    data_dict = {
   'LAST NAME': last_name,  #first name
   'FIRST NAME': first_name,  #second name
   'DATE OF BIRTH MMDDYYYY': birth,
   'MED RECPATIENT IDENTIFIER': licence,  #passport 
   'ETHNICITY': 'ASIA',  
   'ADDRESS': street,  #
   'CITY': city,  #city
   'STATEPROVINCE': state,  #state
   'POSTAL CODE': zipcode,
   'COUNTRY':'USA',
   'PHONE': tel, #phone
   'EMAIL': email,  #email
   'Collection Date': test_date,  #report date
   'Date MMDDYYYY': test_date,
   'Date11_es_:signer:date': test_date,
   'Text10': first_name + " " + last_name
   }


    ok_name =  os.path.join(dstdir, "{}_{}.pdf".format(last_name, first_name))
    nck_name = os.path.join(dstdir, "{}_{}-.pdf".format(last_name, first_name)) 

    if dualtest:
        template_file ="dualtest.pdf"
    else:
        template_file ="newtemplate.pdf"

    if report_date != test_date:
        template_file ="apostl_tom.pdf"
    else:
        template_file ="apostl_tod.pdf"

    if gender == "female" or gender == "Female":
        fill.write_fillable_pdf(template_file, ".tmp.pdf", data_dict, "apostl")
        if shit:
            chsex.to_female(".tmp.pdf", nck_name, "apostl")
        else:
            chsex.to_female(".tmp.pdf", ok_name, "apostl")
    else:
        if shit:
            fill.write_fillable_pdf(template_file, nck_name, data_dict, "apostl")
        else:
            fill.write_fillable_pdf(template_file, ok_name, data_dict, "apostl")

